package CustomClassFile.CronJob;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import net.sf.json.JSONObject;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.general.StaticObj;
import weaver.interfaces.schedule.BaseCronJob;
import weaver.interfaces.schedule.CronJob;

public class InsertHRKQ extends BaseCronJob implements CronJob {
	private final boolean preMonth = true;// 是否同步上月数据
	//
	private final BaseBean writelog=new BaseBean();
	
	public void execute() {
		long startTime = System.currentTimeMillis(); // 获取开始时间
		Map<String,Object> map=syncHRdata();
		long endTime = System.currentTimeMillis();
		long usetime = (endTime - startTime) / 1000;
		map.put("usetime", usetime + "s");
		JSONObject jsonObject = JSONObject.fromObject(map) ;
		writelog.writeLog("打卡写入HR:"+jsonObject.toString());
	}

	// 查询OA 考勤数据
	private List<AcardRecord> getOADateKq(Date date) {
		Date dateparampreMonth = DateUtil.dateMonthCalculate(date, -1);
		String patten = "yyyy-MM";
		String date1 = DateUtil.dateString(date, patten);
		String date2 = DateUtil.dateString(dateparampreMonth, patten);
		RecordSet rs = new RecordSet();
		String sql = "select k.*,h.outkey,h.workcode,h.lastname from uf_kqxx k INNER JOIN  "
				+ "hrmresource  h on k.kqry=h.id where convert(VARCHAR(7),k.pbrq)='" + date1 + "'";
		if (preMonth) {
			sql += " or convert(VARCHAR(7),k.pbrq)='" + date2 + "'";
		}
		writelog.writeLog("查询OA sql:"+sql);
		rs.executeQuery(sql);
		List<AcardRecord> list = new ArrayList<>();
		while (rs.next()) {
			String id = rs.getString("id");
			String kqry = rs.getString("kqry");
			String kqrq = StringUtils.trim(rs.getString("kqrq"));
			String kqsj = StringUtils.trim(rs.getString("kqsj"));
			String eid = rs.getString("eid");
			String pbrq = StringUtils.trim(rs.getString("pbrq"));
			String name = rs.getString("lastname");
			String workcode = rs.getString("workcode");
			String outkey = rs.getString("outkey");
			String remark = "OA_" + pbrq + "_" + id;
			String xDateTime = kqrq + " " + kqsj+":00";
			String handflag=rs.getString("handflag");
			AcardRecord inserthrkq = new AcardRecord();
			inserthrkq.setBadge(workcode);
			inserthrkq.setEID(outkey);
			inserthrkq.setName(name);
			inserthrkq.setRemark(remark);
			inserthrkq.setxDateTime(xDateTime);
			inserthrkq.setOA_ID(id);
			inserthrkq.setHandFlag(handflag);
			if (isValidDate(xDateTime)) { //如果字符串满足时间格式，则放入插入数据中
				list.add(inserthrkq);
			}else {
				writelog.writeLog("有考勤数据打卡时间格式异常,排除同步到HR:"+JSONObject.fromObject(inserthrkq).toString());
			}
			
		}

		return list;
	}

	// 查询HR考勤数据，获取OA插入hr数据的标识remark(组成为：OA_yyyy-MM-dd_id(id为OA数据id))
	private List<String> getDateKq(Date date) {
		Date dateparampreMonth = DateUtil.dateMonthCalculate(date, -1);
		String patten = "yyyy-MM";
		String remark1 = "OA_" + DateUtil.dateString(date, patten);
		String remark2 = "OA_" + DateUtil.dateString(dateparampreMonth, patten);
		List<String> remarks = new ArrayList<>();

		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB_WRITE"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		try {
			// 默认查询本月和上月同步过的数据库
			String sql = "select * from Acard_record where remark like concat(?,'%')";
			if (preMonth) {
				sql += " or remark like concat(?,'%')";
			}
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, remark1);
			if (preMonth) {
				ps.setString(2, remark2);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String remark = rs.getString("remark");
				String[] str = remark.split("_");
				try {
					String id = str[2];
					remarks.add(id);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return remarks;
	}

	private Map<String,Object> syncHRdata() {
		Date date = new Date();
		Map<String,Object> map=new HashMap<>();
		List<String> ids = getDateKq(date);// 已经同步的考勤数据OA的id;
		List<AcardRecord> lists = getOADateKq(date);
		List<AcardRecord> insertData = new ArrayList<>();
		int inserttotal=0;
		for (AcardRecord record : lists) {
			if (!ids.contains(record.getOA_ID())) {// 排除已同步过的数据
				insertData.add(record);
				inserttotal++;
			}
		}
		// 开始写入数据库
		insertHRdata(insertData);
		map.put("total", lists.size());
		map.put("insert", inserttotal);
		map.put("update", 0);
		return map;
	}

	private void insertHRdata(List<AcardRecord> list) {

		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB_WRITE"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection con = ds.getConnection();

		try {
			String sql = "insert into Acard_record(eid,badge,name,cardid,xdateTime,remark,handflag) VALUES(?,?,?,?,?,?,?)";
			con.setAutoCommit(false);
			PreparedStatement ps = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				AcardRecord record = list.get(i);
				ps.setString(1, record.getEID());
				ps.setString(2, record.getBadge());
				ps.setString(3, record.getName());
				ps.setString(4, record.getEID());
				ps.setString(5, record.getxDateTime());
				ps.setString(6, record.getRemark());
				ps.setString(7, record.getHandFlag());
				ps.addBatch();
				// 1w条记录插入一次
				if (i % 5000 == 0) {
					ps.executeBatch();
					con.commit();
					ps.clearBatch();
				}
			}
			// 最后插入不足1w条的数据
			ps.executeBatch();
			con.commit();
		} catch (SQLException e) {
			writelog.writeLog(e);
			e.printStackTrace();
		}
	}


	
//	private void updateHRdata(List<AcardRecord> list){
//		
//		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
//				.getServiceByFullname(("datasource.HRDB_WRITE"), weaver.interfaces.datasource.DataSource.class);
//		java.sql.Connection con = ds.getConnection();
//		
//		try {
//			String sql = "update aCard_Record set xDateTime=? where remark='OA_2019-02-06_70586'";
//			con.setAutoCommit(false);
//			PreparedStatement ps = con.prepareStatement(sql);
//			for(int i=1; i<list.size(); i++){
//				AcardRecord record = list.get(i);
//				ps.setString(1,record.getxDateTime());
//			    ps.addBatch();
//			    // 1w条记录插入一次
//			    if (i % 5000 == 0){
//			         ps.executeBatch();
//			         con.commit();
//			         ps.clearBatch();
//			     }
//			}
//			// 最后插入不足1w条的数据
//			ps.executeBatch();
//			con.commit();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
	
	private  boolean isValidDate(String str) {
		boolean convertSuccess = true;// 指定日期格式为四位年-两位月份-两位日期，注意yyyy-MM-dd区分大小写；
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			// 设置lenient为false. 否则SimpleDateFormat会比较宽松地验证日期，比如2007/02/29会被接受，并转换成2007/03/01
			format.setLenient(false);
			format.parse(str);
		} catch (ParseException e) {
			// e.printStackTrace();
			// 如果throw java.text.ParseException或者NullPointerException，就说明格式不对
			convertSuccess = false;
		}
		return convertSuccess;
	}
}
